import pandas as pd
import mysql_util as sql_util
import mysql_util_starrocks as starrocks_util
import json

"""查询进入养殖管理的人的场权限"""
"""导出来的数据导入到猪博士report.farm_event_user_farm"""

def find_ids():
    sql = "select distinct a.uid external_id from master_data.cp_utm_event_log a where 1= 1 \
    and a.create_time > '2024-01-01 00:00:00' \
    and a.utm_souce in ('Farm_ChangeFarmer_ClickHogpenManage')"
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    return rs


sql_org = "select distinct o1.id as region_id, o1.code as region_code, o1.name as region_name, \
			 o2.id as province_id, o2.code as province_code, o2.name as province_name, \
			 o3.id as company_id, o3.code as company_code, o3.name as company_name, \
			 o4.id as org_id, o4.code as org_code, o4.name as org_name, \
			 o5.id as farm_id, o5.code as farm_code, o5.name as farm_name, o5.flag, o5.project, o5.business_id  \
			 from pc_organization o1, pc_organization o2, pc_organization o3, pc_organization o4, \
			 pc_organization o5, pc_user_organization uo \
			 where o1.id = o2.parent_id and o2.id = o3.parent_id and o3.id = o4.parent_id and o4.id = o5.parent_id \
			 and o1.enabled = 1 and o2.enabled = 1 and o3.enabled = 1 and o4.enabled = 1 and o5.enabled = 1 \
			 and ((uo.level = 1 and uo.organization_id = o1.id) or (uo.level = 2 and uo.organization_id = o2.id) \
			 or (uo.level = 3 and uo.organization_id = o3.id) or (uo.level = 4 and uo.organization_id = o4.id) \
			 or (uo.level = 5 and uo.organization_id = o5.id)) and uo.user_id = '{}'"

if __name__ == '__main__':
    org_codes = []
    farm_orgs = []
    external_id_list = []
    flag_list = []
    province_list = []

    ids = find_ids()
    # mysql的连接
    conn_mysql = sql_util.get_connection()
    cursor_mysql = conn_mysql.cursor();
    for row in ids:
        external_id = row['external_id']
        sql = "select * from pc_user u where u.external_id = '{}'".format(external_id)
        rs = sql_util.select_by_sql(sql)
        rs = json.loads(rs)
        print('external_id=' + external_id)
        if len(rs) > 0:
            id = rs[0]['id']
            # 查询用户的场
            sql_org2 = sql_org.format(id)
            rs = sql_util.select_by_sql2(sql_org2, cursor_mysql)
            rs = json.loads(rs)
            if len(rs) > 0:
                for org in rs:
                    org_code = rs[0]['org_code']
                    farm_code = rs[0]['farm_code']
                    flag = rs[0]['flag']
                    province = rs[0]['province_name']
                    external_id_list.append(external_id)
                    org_codes.append(org_code)
                    farm_orgs.append(farm_code)
                    flag_list.append(flag)
                    province_list.append(province)
        else:
            continue

    excel_data = {}
    excel_data['external_id'] = external_id_list
    excel_data['org_code'] = org_codes
    excel_data['farm_org'] = farm_orgs
    excel_data['flag'] = flag_list
    excel_data['province'] = province_list

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + 'farm_event_user.xlsx', index=False)
